How to Add WITH (NOLOCK) and Handle Parameter Sniffing in Entity Framework

TLDR

• By implementing DbCommandInterceptor, you can dynamically inject WITH (NOLOCK) hints and OPTION (OPTIMIZE FOR UNKNOWN) before Entity Framework executes SQL.
• WITH (NOLOCK) can prevent blocking during read operations, but it should be avoided for INSERT/UPDATE/DELETE and precise queries (e.g., First(), Single()) to prevent reading uncommitted data or affecting subsequent operations.
• OPTION (OPTIMIZE FOR UNKNOWN) can effectively mitigate performance issues caused by Parameter Sniffing in SQL Server.
• Interceptors must be registered to EF Core via the AddInterceptors method of DbContextOptionsBuilder.

WARNING

This implementation may be considered an Anti-Pattern in some modern architectures. It is recommended to prioritize using an RCSI (Read Committed Snapshot Isolation) architecture or use EF Core's TagWith feature for more granular control. For detailed recommendations, please refer to: RCSI Introduction and Improved Entity Framework Locking Hint Interceptor

TIP

A complete, executable sample for this article can be found here: CloudyWing/EfCoreSqlHintInterceptorSample.

Intercepting SQL Commands Using Interceptor

In Entity Framework Core, IDbCommandInterceptor allows developers to intercept and modify DbCommand before executing database operations. This is very useful for scenarios where you need to adjust SQL syntax globally (e.g., injecting performance hints).

Implementing Interceptor Logic

When might you encounter this issue: When you need to globally add NOLOCK hints for read queries, or resolve poor execution plans caused by Parameter Sniffing.

By inheriting from DbCommandInterceptor and overriding methods such as ReaderExecuting and ScalarExecuting, you can dynamically modify the CommandText:

public class FixDbCommandInterceptor : DbCommandInterceptor {
    private static readonly RegexOptions regexOptions = RegexOptions.Multiline | RegexOptions.IgnoreCase;
    private static readonly Regex cudRegex = new(@"\b(INSERT|UPDATE|DELETE)\b", regexOptions);
    private static readonly Regex tableAliasRegex = new(
        @"(?<tableAlias>(FROM|JOIN)\s+\[\w+\]\s+AS\s+\[\w+\])",
        regexOptions
    );

    // Override pre-execution methods
    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<DbDataReader> result) {
        FixCommand(command);
        return base.ReaderExecuting(command, eventData, result);
    }

    private static void FixCommand(IDbCommand command) {
        string commandText = command.CommandText;

        // Exclude modification statements to avoid affecting data consistency
        if (cudRegex.IsMatch(commandText)) {
            return;
        }

        // Exclude precise queries (e.g., First/Single) to avoid reading uncommitted data
        if (!commandText.Contains("TOP(1)") && !commandText.Contains("TOP(2)")) {
            commandText = tableAliasRegex.Replace(commandText, "${tableAlias} WITH (NOLOCK)");
        }

        // Add OPTION (OPTIMIZE FOR UNKNOWN) to handle Parameter Sniffing
        commandText = commandText.TrimEnd(';') + " OPTION (OPTIMIZE FOR UNKNOWN);";

        command.CommandText = commandText;
    }
}

Registering the Interceptor

The interceptor must be registered when DbContext is initialized. It can be configured in OnConfiguring or specified during DI container injection:

// Register in DI
services.AddDbContext<TestDbContext>(options => {
    options
        .UseSqlServer(DbConnectionString)
        .AddInterceptors(new FixDbCommandInterceptor());
});

Processing Results and Verification

With the interceptor above, when queries like ToList() are executed, the SQL syntax will automatically be injected with the hints.

When might you encounter this issue: When the default SQL syntax generated by EF Core lacks performance hints, leading to lock waits in high-concurrency environments, or when SQL Server selects a suboptimal execution plan due to varying parameter values.

Execution Result Example

When executing context.Tests.ToList(), the generated SQL will automatically include WITH (NOLOCK) and OPTION:

SELECT [t].[Id], [t].[TestBit], [t].[TestDateTime], [t].[TestGuid], [t].[TestInt], [s].[Id], [s].[TestId]
FROM [Test] AS [t] WITH (NOLOCK)
LEFT JOIN [SubTest] AS [s] WITH (NOLOCK) ON [t].[Id] = [s].[TestId]
ORDER BY [t].[Id] OPTION (OPTIMIZE FOR UNKNOWN);

WARNING

The processing above lacks verification for actual usage; please adjust it according to your own specific circumstances.

Change Log

• 2024-07-18 Initial version created.
• 2026-05-29 Added link to the corresponding GitHub sample project.